package org.goyo.main.domain.dao.report.impl;

import java.util.List;

import org.goyo.main.domain.dao.BaseDAO;
import org.goyo.main.domain.dao.report.ISysReport;
import org.goyo.main.domain.entity.report.InventoryReport;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;
@Repository
public class SysReportImpl extends BaseDAO implements ISysReport{

	@Override
	public List<InventoryReport> getInventoryReport() {
		Session session = this.getSession();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT t.uuid As uuid,t.name AS materialName,t.number AS materialNumber,t2.inQty,t4.dispQty,t4.outQty,t5.name baseUtilName");
		sql.append(" FROM t_material t ");
		sql.append(" LEFT JOIN ");

		sql.append(" (SELECT  ");
		sql.append(" materialId,");
		//sql.append("-- 采购入库数量+库存盘点数量");
		sql.append(" IFNULL(SUM(in_storage_qty),0) + (SELECT IFNULL(SUM(difference_qty),0) FROM t_inventory_check_entry t1 WHERE t1.difference_way = 'rk' AND t1.materialId) inQty");
		sql.append(" FROM t_pur_in_storage_entry  GROUP BY materialId) t2 ON t2.materialId = t.uuid");
		
		sql.append(" LEFT JOIN ");
		
		sql.append(" (SELECT materialId,");
		//sql.append(" -- 销售出库数量 + 库存盘点数量");
		sql.append(" IFNULL(SUM(sum_out_qty),0) + (SELECT IFNULL(SUM(difference_qty),0) FROM t_inventory_check_entry t3 WHERE t3.difference_way = 'ck' AND t3.materialId) outQty,");
		sql.append(" IFNULL(SUM(sum_disp_qty),0) dispQty ");
		sql.append(" FROM t_sale_order_entry  GROUP BY materialId) t4 ON t4.materialId = t.uuid");
		
		sql.append(" LEFT JOIN ");
		
		sql.append(" t_base_unit_measure t5 ON t5.uuid = t.base_unit ");
		return session.createSQLQuery(sql.toString()).addEntity(InventoryReport.class).list();
	}

	@Override
	public InventoryReport findByMaterialId(String materialId) {
		String hql = "from InventoryReport where material.uuid =?";
		return this.findUnique(hql, new Object[]{materialId});
	}
	
}
